﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;
using System.Configuration;
using System.Threading;
using System.IO;

namespace RZRQ_ChooseStock_Model.Forms
{
    public partial class FormMain : Form
    {
        private const string DBTYPE = "SQLSERVER";
        private SqlConnection sqlConn { get; set; }
        private bool isRunning { get; set; }
        private bool formClosed { get; set; }

        private delegate void SetStatusText(string strStatus);
        private delegate void SetProgressValue(int value);
        private delegate void SetButtonStatus(Button btn, bool isEnabled);
        private delegate void BindDataGridView(DataTable dt);
        private delegate void ShowLoadingImage(bool isShow);
        private delegate void ShowProgressBar(bool isShow);

        public FormMain()
        {
            InitializeComponent();
        }

        private void Calculate()
        {
            SetButton(this.btnExport, false);

            DataTable dt = new DataTable();
            SqlCommand cmd = new SqlCommand();
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandText = "RZRQ.DBO.P_CALCULATE_PC_PEOPLE";
            cmd.Connection = this.sqlConn;
            
            SqlDataReader dr = cmd.ExecuteReader();            
            dt.Load(dr);
            BindDataGrid(dt);

            SetButton(this.btnExport, true);
        }

        private void FormMain_Load(object sender, EventArgs e)
        {
            this.sqlConn = new SqlConnection();
            this.sqlConn.ConnectionString = ConfigurationManager.ConnectionStrings[DBTYPE].ConnectionString;

            try
            {
                this.sqlConn.Open();
                //MessageBox.Show("数据库连接成功!", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
            }
            catch (Exception ex)
            {
                MessageBox.Show("数据库连接失败! 请检查配置文件中的连接字符串或网络情况", "错误", MessageBoxButtons.OK, MessageBoxIcon.Error);
                this.Close();
            }

            this.isRunning = false;
            this.formClosed = false;
        }

        private void btnBrowse_Click(object sender, EventArgs e)
        {
            if (this.openFileDialog.ShowDialog() == System.Windows.Forms.DialogResult.OK)
            {
                this.txtFilePath.Text = this.openFileDialog.FileName;
                SetButton(this.btnImport, true);
            }
        }

        private void FormMain_DragDrop(object sender, DragEventArgs e)
        {
            this.txtFilePath.Text = ((System.Array)e.Data.GetData(DataFormats.FileDrop)).GetValue(0).ToString();
            SetButton(this.btnImport, true);
        }

        private void FormMain_DragEnter(object sender, DragEventArgs e)
        {
            if (e.Data.GetDataPresent(DataFormats.FileDrop))
                e.Effect = DragDropEffects.Link;
            else e.Effect = DragDropEffects.None; 
        }

        private void btnImport_Click(object sender, EventArgs e)
        {
            if (MessageBox.Show("确定要导入数据吗？ 此操作将会持续10分钟左右", "提示", MessageBoxButtons.YesNo, MessageBoxIcon.Question) == System.Windows.Forms.DialogResult.Yes)
            {
                Thread thread = new Thread(StartImport);
                thread.Start();
            }           
        }

        private void ClearTables()
        {
            SqlCommand cmd = new SqlCommand();
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandText = "RZRQ.DBO.P_CLEAR_TABLES";
            cmd.Connection = this.sqlConn;
            cmd.ExecuteNonQuery();

            SetButton(this.btnExport,true);
        }

        

        private void SetStatus(string strStatus)
        {
            SetStatusText objSetStatus = new SetStatusText(SetStatus);

            if (this.statusStrip.InvokeRequired)
            {
                try
                {
                    BeginInvoke(objSetStatus, strStatus);
                }
                catch(Exception ex)
                {
                    MessageBox.Show(ex.ToString());
                }
            }
            else
            {
                this.lblStatus.Text = strStatus;
            }
        }

        private void SetProgress(int value)
        {
            SetProgressValue objSetProgress = new SetProgressValue(SetProgress);

            if (this.statusStrip.InvokeRequired)
            {
                try
                {
                    BeginInvoke(objSetProgress, value);
                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.ToString());
                }
            }
            else
            {
                this.progressBar.Value = value;
            }
        }

        private void SetButton(Button btn, bool isEnabled)
        {
            SetButtonStatus objSetButton = new SetButtonStatus(SetButton);

            if (btn.InvokeRequired)
            {
                try
                {
                    BeginInvoke(objSetButton, new object[] { btn, isEnabled });
                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.ToString());
                }
            }
            else
            {
                btn.Enabled = isEnabled;
            }
        }

        private void BindDataGrid(DataTable dt)
        {
            BindDataGridView objBindDataGrid = new BindDataGridView(BindDataGrid);

            if (this.dgvResult.InvokeRequired)
            {
                try
                {
                    BeginInvoke(objBindDataGrid, dt);
                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.ToString());
                }
            }
            else 
            {
                this.dgvResult.DataSource = dt;
            }
        }

        private void ShowLoading(bool isShow)
        {
            ShowLoadingImage objShowLoading = new ShowLoadingImage(ShowLoading);

            if (this.statusStrip.InvokeRequired)
            {
                try
                {
                    BeginInvoke(objShowLoading, isShow);
                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.ToString());
                }
            }
            else
            {
                this.picLoading.Visible = isShow;
            }
        }

        private void ShowProgress(bool isShow)
        {
            ShowProgressBar objShowProgress = new ShowProgressBar(ShowProgress);

            if (this.statusStrip.InvokeRequired)
            {
                try
                {
                    BeginInvoke(objShowProgress, isShow);
                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.ToString());
                }
            }
            else
            {
                this.progressBar.Visible = isShow;
            }
        }


        private void ImportData()
        {
            SetButton(this.btnCalculate,false);
            SetButton(this.btnImport,false);
            
            SetStatus("正在初始化表");
            ClearTables();
            SetStatus("初始化表完成");
            
            Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();
            Microsoft.Office.Interop.Excel.Workbooks workbooks = xlApp.Workbooks;
            Microsoft.Office.Interop.Excel.Workbook workbook = workbook = workbooks.Add(this.txtFilePath.Text);
            Microsoft.Office.Interop.Excel.Sheets sheets = workbook.Sheets;
            Microsoft.Office.Interop.Excel.Worksheet sheet = (Microsoft.Office.Interop.Excel.Worksheet)sheets.get_Item(1);

            SqlCommand cmd = new SqlCommand();
            cmd.CommandType = CommandType.Text;
            cmd.Connection = this.sqlConn;

            string strTemp = "";
            string strMarket = "";
            string strStockCode = "";
            string strStockName = "";
            string strDate = "";
            string strPrice = "";
            string[] aryStocks;

            SetStatus("正在导入数据，该过程可能会持续10分钟左右，请耐心等待");
            try
            {
                for (int i = 1; i <= sheet.UsedRange.Rows.Count; i++)
                {
                    for (int j = 1; j <= sheet.UsedRange.Columns.Count; j++)
                    {
                        if (j == 1)
                        {
                            continue;
                        }
                        else
                        {
                            //取股票代码及名称
                            strTemp = ((Microsoft.Office.Interop.Excel.Range)sheet.Cells[1, j]).Text.ToString();
                            strStockName = ((Microsoft.Office.Interop.Excel.Range)sheet.Cells[2, j]).Text.ToString();
                            aryStocks = strTemp.Split('.');
                            if (aryStocks[1].ToUpper() == "SH")
                            {
                                strMarket = "1";
                            }
                            else
                            {
                                strMarket = "2";
                            }
                            strStockCode = aryStocks[0];
                        }

                        if (i == 1)
                        {
                            if (j > 1)
                            {
                                //插入股票代码TB_STOCK表
                                cmd.CommandText = "INSERT INTO TB_STOCK(MARKET_CODE, SEC_CODE, SEC_NAME) VALUES('" + strMarket + "', '" + strStockCode + "', '" + strStockName + "')";
                                cmd.ExecuteNonQuery();
                            }
                        }
                        else if (i == 2 || i == 3)
                        {
                            break;
                        }
                        else
                        {
                            //插入日期表TB_TRADEDATE
                            if (j == 2)
                            {
                                strDate = ((Microsoft.Office.Interop.Excel.Range)sheet.Cells[i, 1]).Text.ToString();
                                cmd.CommandText = "INSERT INTO TB_TRADEDATE(TRADEDATE) VALUES('" + strDate + "')";
                                cmd.ExecuteNonQuery();
                            }

                            //插入价格表TB_PRICE
                            strPrice = ((Microsoft.Office.Interop.Excel.Range)sheet.Cells[i, j]).Text.ToString();
                            cmd.CommandText = "INSERT INTO TB_PRICE(TRADEDATE, MARKET_CODE, SEC_CODE, PRICE) VALUES('" + strDate + "', '" + strMarket + "', '" + strStockCode + "', " + strPrice + ")";
                            cmd.ExecuteNonQuery();
                        }
                    }

                    if (i == sheet.UsedRange.Rows.Count)
                    {
                        SetProgress(95);
                    }
                    else 
                    {
                        SetProgress(Convert.ToInt32(Convert.ToDouble(i) / sheet.UsedRange.Rows.Count * 95));
                    }
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.ToString());
                ReleaseCOM(sheet);
                ReleaseCOM(sheets);
                ReleaseCOM(workbook);
                ReleaseCOM(workbooks);
                ReleaseCOM(xlApp);
                SetStatus("数据导入失败");
            }

            ReleaseCOM(sheet);
            ReleaseCOM(sheets);
            ReleaseCOM(workbook);
            ReleaseCOM(workbooks);
            ReleaseCOM(xlApp);

            SetStatus("导入数据完成");            
        }

        private void StartImport()
        {
            ShowProgress(true);
            SetButton(this.btnExport, false);
            SetButton(this.btnCalculate, false);
            SetButton(this.btnImport, false);
            this.isRunning = true;
            this.ImportData();
            InitFirstDayPrice();
            CalculateGuaranteeRatio();
            SetProgress(100);
            MessageBox.Show("数据导入成功！", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
            SetButton(this.btnExport, true);
            SetButton(this.btnCalculate, true);
            SetButton(this.btnImport, true);
            this.isRunning = false;
        }

        private void btnCalculate_Click(object sender, EventArgs e)
        {
            this.picLoading.Visible = true;
            Thread thread = new Thread(StartCalculate);
            thread.Start();
        }

        private void StartCalculate()
        {
            SetButton(this.btnCalculate, false);
            ShowProgress(false); 
            ShowLoading(true);
            SetStatus("正在计算追加率，请稍后");
            this.isRunning = true;
            this.Calculate();
            ShowLoading(false);
            SetStatus("计算追加率完成");
            this.isRunning = false;
            SetButton(this.btnCalculate, true);
        }

        private void InitFirstDayPrice()
        {
            SetStatus("正在初始化期初证券价格");
            SqlCommand cmd = new SqlCommand();
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandText = "RZRQ.DBO.P_INIT_FIRST_DAY_PRICE";
            cmd.Connection = this.sqlConn;
            cmd.ExecuteNonQuery();
            SetStatus("初始化期初证券价格完成");
        }

        private void CalculateGuaranteeRatio()
        {
            SetStatus("正在计算客户维持担保比例");
            SqlCommand cmd = new SqlCommand();
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandText = "RZRQ.DBO.P_CALCULATE_GUARANTEE_RATIO";
            cmd.Connection = this.sqlConn;
            cmd.CommandTimeout = 300;
            cmd.ExecuteNonQuery();
            SetStatus("计算客户维持担保比例完成");
        }

        private void dgvResult_RowStateChanged(object sender, DataGridViewRowStateChangedEventArgs e)
        {
            e.Row.HeaderCell.Value = (e.Row.Index + 1).ToString();
        }

        private void ExportExcel(DataGridView myDGV)
        {
            SetButton(this.btnExport, false);
            string fileName = "标的证券追加率";
            string saveFileName = "";
            SaveFileDialog saveDialog = new SaveFileDialog();
            saveDialog.DefaultExt = "xlsx";
            saveDialog.Filter = "Excel文件|*.xlsx";

            saveDialog.FileName = fileName;
            saveDialog.ShowDialog();
            saveFileName = saveDialog.FileName;
            if (saveFileName.IndexOf(":") < 0) return; //被点了取消 
            Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();
            if (xlApp == null)
            {
                MessageBox.Show("无法创建Excel对象，可能您的机子未安装Excel");
                return;
            }
            Microsoft.Office.Interop.Excel.Workbooks workbooks = xlApp.Workbooks;
            Microsoft.Office.Interop.Excel.Workbook workbook = workbooks.Add(Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet);
            Microsoft.Office.Interop.Excel.Worksheet worksheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets[1];//取得sheet1

            ShowLoading(true);
            
            //写入标题
            for (int i = 0; i < myDGV.ColumnCount; i++)
            {
                worksheet.Cells[1, i + 1] = myDGV.Columns[i].HeaderText;
            }
            //写入数值
            for (int r = 0; r < myDGV.Rows.Count; r++)
            {
                for (int i = 0; i < myDGV.ColumnCount; i++)
                {
                    if (myDGV.Columns[i].Name == "colSecCode")
                    {
                        worksheet.Cells[r + 2, i + 1] = "'" + myDGV.Rows[r].Cells[i].Value;
                    }
                    else 
                    {
                        worksheet.Cells[r + 2, i + 1] = myDGV.Rows[r].Cells[i].Value;
                    }                    
                }
                System.Windows.Forms.Application.DoEvents();
            }
            worksheet.Columns.EntireColumn.AutoFit();//列宽自适应
            
            if (saveFileName != "")
            {
                try
                {
                    workbook.Saved = true;
                    workbook.SaveCopyAs(saveFileName);
                }
                catch (Exception ex)
                {
                    MessageBox.Show("导出文件时出错,文件可能正被打开！\n" + ex.Message, "错误", MessageBoxButtons.OK, MessageBoxIcon.Error);
                    ShowLoading(false);
                    return;
                }
            }
            xlApp.Quit();
            GC.Collect();//强行销毁 
            ShowLoading(false);
            SetButton(this.btnExport, true);
            MessageBox.Show(fileName + "保存成功", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
        }

        private void btnExport_Click(object sender, EventArgs e)
        {
            Thread thread = new Thread(StartExport);
            thread.Start();
        }

        private void StartExport()
        {
            SetButton(this.btnExport, false);
            this.isRunning = true;
            ExportExcel(this.dgvResult);
            this.isRunning = false;
            SetButton(this.btnExport, true);
        }

        private void btnTest_Click(object sender, EventArgs e)
        {
            //System.Data.OleDb.OleDbConnection cnn = new System.Data.OleDb.OleDbConnection();
            //cnn.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=c:\\price.xlsx;Extended Properties=\"Excel 12.0;HDR=No\"";
            //try
            //{
            //    cnn.Open();
            //    //MessageBox.Show("success");
            //    System.Data.OleDb.OleDbCommand cmd = new System.Data.OleDb.OleDbCommand();
            //    cmd.CommandText = "select * from [Sheet1$]";
            //    cmd.Connection = cnn;
            //    System.Data.OleDb.OleDbDataReader dr = cmd.ExecuteReader();
            //    dr.Read();
            //    MessageBox.Show("colums:" + dr.FieldCount);
            //}
            //catch (Exception ex)
            //{
            //    MessageBox.Show(ex.ToString());
            //}

        }

        private void ReleaseCOM(object pObj)
        {
            try
            {
                System.Runtime.InteropServices.Marshal.ReleaseComObject(pObj);
            }
            catch
            {
                throw new Exception("释放资源时发生错误！");
            }
            finally
            {
                pObj = null;
            }
        }

        private void btnHelp_Click(object sender, EventArgs e)
        {
            FormHelp frmHelp = new FormHelp();
            frmHelp.ShowDialog();
        }

        private void btnExit_Click(object sender, EventArgs e)
        {
            Exit();
        }

        private void Exit()
        {
            if (this.isRunning)
            {
                MessageBox.Show("任务正在执行，请稍后再退出！", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
            }
            else
            {
                if (!this.formClosed)
                {
                    this.formClosed = true;
                    this.sqlConn.Close();
                    this.Close();
                }
            }
        }

        private void FormMain_FormClosing(object sender, FormClosingEventArgs e)
        {
            Exit();
        }
    }
}
